package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.mysql.jdbc.PreparedStatement;
import dto.StudentAnswerDto;

public class StudentAnswerDao {

	public String insertStudentAnswer(StudentAnswerDto studentAnswer) {

		if (studentAnswer == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO student_answer (student_student_id, questions_question_no, answer) VALUES (?,?,?)"
				+ " on duplicate key update answer=values(answer)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, studentAnswer.getStudentId());
			preparedStatement.setInt(2, studentAnswer.getQuestionNo());
			preparedStatement.setInt(3, studentAnswer.getAnswer());

			preparedStatement.execute();
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteStudentAnswer(String studentAnswerId) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM student_answer WHERE student_student_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, studentAnswerId);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public ArrayList<StudentAnswerDto> getAllStudentAnswer() {

		ArrayList<StudentAnswerDto> listStudentAnswer = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM student_answer";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				StudentAnswerDto studentAnswer = new StudentAnswerDto();
				studentAnswer.setStudentId(resultSet
						.getString("student_student_id"));
				studentAnswer.setAnswer(resultSet.getInt("answer"));
				studentAnswer.setQuestionNo(resultSet
						.getInt("questions_question_no"));
				listStudentAnswer.add(studentAnswer);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listStudentAnswer;
	}

}
